﻿Imports MySQL.Data.MySqlClient
Imports System.Collections.Generic
Imports System.Web



Public MustInherit Class baseMySQL


    Public Sub New()

    End Sub

    ''' <summary>
    ''' Creates a MySqlConnection associated with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public MustOverride Function GetMySQLConnection() As MySqlConnection

    ''' <summary>
    ''' Creates a MySqlConnection associated with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public MustOverride ReadOnly Property Connection() As MySqlConnection

    ''' <summary>
    ''' Creates a GetMySQLCommand and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function GetMySQLCommand() As MySqlCommand
        Dim comm As New MySqlCommand
        comm.Connection = GetMySQLConnection()
        Return comm
    End Function


    ''' <summary>
    ''' Creates a GetMySQLCommand with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function GetMySQLCommand(ByVal CommandText As String) As MySqlCommand
        Dim comm As New MySqlCommand
        comm.Connection = GetMySQLConnection()
        comm.CommandText = CommandText
        Return comm
    End Function

    ''' <summary>
    ''' Creates a GetMySQLCommand with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function GetMySQLCommand(ByVal CommandText As String, ByVal ParamArray params As MySqlParameter()) As MySqlCommand
        Dim comm As New MySqlCommand
        comm.Connection = GetMySQLConnection()
        comm.CommandText = CommandText
        comm.Parameters.AddRange(params)
        Return comm
    End Function

    ''' <summary>
    ''' Uses a MySQLDataAdpter to create a filled table with the given CommandText and returns the first column of that table as an object array.  Uses the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <param name="CommandText"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMySQLFilledColumn(ByVal CommandText As String) As String()
        Dim dt As Data.DataTable = GetMySQLFilledTable(CommandText)
        Dim o As New List(Of String)

        For Each dr As Data.DataRow In dt.Rows
            o.Add(dr.Item(0))
        Next

        Return o.ToArray
    End Function


    ''' <summary>
    ''' Creates a GetMySQLCommand with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMySQLScalarValue(ByVal CommandText As String) As String
        Dim scalarvalue As String
        Dim comm As MySqlCommand = GetMySQLCommand(CommandText)
        comm.Connection.Open()
        scalarvalue = comm.ExecuteScalar().ToString
        comm.Connection.Close()
        Return scalarvalue
    End Function

    ''' <summary>
    ''' Creates a GetMySQLCommand with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMySQLScalarValue(ByVal CommandText As String, ByVal ParamArray params As MySqlParameter()) As String
        Dim comm As New MySqlCommand
        comm.Connection = GetMySQLConnection()
        comm.CommandText = CommandText
        For Each param As MySqlParameter In params
            comm.Parameters.Add(param)
        Next
        comm.Connection.Open()
        Dim out As String = comm.ExecuteScalar()
        comm.Connection.Close()
        Return out
    End Function

    ''' <summary>
    ''' Sets a single scalar value in the mySQL database with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub SetMySQLValue(ByVal CommandText As String)
        Dim comm As MySqlCommand = GetMySQLCommand(CommandText)
        comm.Connection.Open()
        comm.ExecuteNonQuery()
        comm.Connection.Close()
    End Sub
    ''' <summary>
    ''' Creates a MySQLDataAdpter and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function GetMySQLDataAdapter() As MySqlDataAdapter
        Dim DA As New MySqlDataAdapter(GetMySQLCommand)
        Return DA
    End Function
    ''' <summary>
    ''' Creates a MySQLDataAdpter with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function GetMySQLDataAdapter(ByVal CommandText As String, ByVal ParamArray params As MySqlParameter()) As MySqlDataAdapter
        Dim DA As New MySqlDataAdapter(GetMySQLCommand(CommandText, params))
        Return DA
    End Function

    ''' <summary>
    ''' Creates a MySQLDataAdpter with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function GetMySQLDataAdapter(ByVal CommandText As String) As MySqlDataAdapter
        Dim DA As New MySqlDataAdapter(GetMySQLCommand(CommandText))
        Return DA
    End Function

    ''' <summary>
    ''' Creates a MySQLDataAdpter with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function GetMySQLDataAdapter(ByVal CommandText As String, ByVal UseCommandBuilder As Boolean) As MySqlDataAdapter
        Dim DA As New MySqlDataAdapter(GetMySQLCommand(CommandText))
        Dim cb As MySqlCommandBuilder
        If UseCommandBuilder Then
            cb = New MySqlCommandBuilder(DA)
            cb.SetAllValues = False
            DA.DeleteCommand = cb.GetDeleteCommand
            DA.UpdateCommand = cb.GetUpdateCommand
            DA.InsertCommand = cb.GetInsertCommand

            DA.InsertCommand.Connection = DA.SelectCommand.Connection
            DA.UpdateCommand.Connection = DA.SelectCommand.Connection
            DA.DeleteCommand.Connection = DA.SelectCommand.Connection
        End If


        Return DA
    End Function

    ''' <summary>
    ''' Creates a MySQLDataAdpter with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function GetMySQLDataAdapter(ByVal [SELECT] As String, ByVal INSERT As String, ByVal UPDATE As String, ByVal DELETE As String) As MySqlDataAdapter
        Dim DA As New MySqlDataAdapter(GetMySQLCommand([SELECT]))
        Dim Ins As New MySqlCommand(INSERT, DA.SelectCommand.Connection)
        Dim Upd As New MySqlCommand(UPDATE, DA.SelectCommand.Connection)
        Dim Del As New MySqlCommand(DELETE, DA.SelectCommand.Connection)
        DA.InsertCommand = Ins
        DA.UpdateCommand = Upd
        DA.DeleteCommand = Del
        Return DA
    End Function

    '''' <summary>
    '''' Creates a MySQLDataAdpter with the given CommandText and CommandBuilder and associates it with the site's Live or TestDB as appropriate.
    '''' </summary>
    '''' <param name="SELECTCommandText">SeLECT statement for use with FILL(): SELECT * FROM `Table` WHERE...</param>
    '''' <param name="PKEY_SELECTforinsert">Used to retrieve row data after insert occurs: SELECT * FROM `Table` WHERE id=LAST_INSERT_ID();</param>
    '''' <returns></returns>
    '''' <remarks></remarks>
    'Public Overloads Function GetMySQLDataAdapter(ByVal SELECTCommandText As String, ByVal PKEY_SELECTforinsert As String) As MySqlDataAdapter
    '    Dim da As MySqlDataAdapter = GetMySQLDataAdapter(SELECTCommandText, True)
    '    'If UseCommandBuilder Then
    '    'Get the PKEY of the new row
    '    da.InsertCommand.CommandText = da.UpdateCommand.CommandText & PKEY_SELECTforinsert
    '    da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
    '    da.MissingMappingAction = MissingMappingAction.Passthrough
    '    'End If

    '    Return da
    'End Function

    '''' <summary>
    '''' Creates a MySQLDataAdpter with the given CommandText and CommandBuilder and associates it with the site's Live or TestDB as appropriate.
    '''' </summary>
    '''' <param name="SELECTCommandText">SeLECT statement for use with FILL(): SELECT * FROM `Table` WHERE...</param>
    '''' <param name="Tablename">Used to build command to retrieve rowdata after insert occurs</param>
    '''' <param name="PKEYColumnName">Used to build command to retrieve rowdata after insert occurs</param>
    '''' <returns></returns>
    '''' <remarks></remarks>
    'Public Overloads Function GetMySQLDataAdapter(ByVal SELECTCommandText As String, ByVal Tablename As String, ByVal PKEYColumnName As String) As MySqlDataAdapter
    '    Return GetMySQLDataAdapter(SELECTCommandText, "SELECT * FROM " & Tablename & " WHERE " & PKEYColumnName & "=LAST_INSERT_ID();")
    'End Function

    ''' <summary>
    ''' Uses a MySQLDataAdpter to create a filled table with the given CommandText.  Uses the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <param name="CommandText"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function GetMySQLFilledTable(ByVal CommandText As String, ByVal ParamArray params As MySqlParameter()) As Data.DataTable
        Dim DA As MySqlDataAdapter = GetMySQLDataAdapter(CommandText, params)
        Dim dt As New Data.DataTable
        DA.Fill(dt)
        Return dt
    End Function

    ''' <summary>
    ''' Uses a MySQLDataAdpter to create a filled table with the given CommandText.  Uses the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <param name="CommandText"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Overloads Function GetMySQLFilledTable(ByVal CommandText As String) As Data.DataTable
        Dim DA As MySqlDataAdapter = GetMySQLDataAdapter(CommandText)
        Dim dt As New Data.DataTable
        DA.Fill(dt)
        Return dt
    End Function

    ''' <summary>
    ''' Uses a MySQLDataAdpter to create a filled table with the given CommandText and returns the first row of that table.  Uses the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <param name="CommandText"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMySQLFilledRow(ByVal CommandText As String) As Data.DataRow
        Dim dt As Data.DataTable = GetMySQLFilledTable(CommandText)
        Dim dr As Data.DataRow = dt.Rows(0)
        Return dr
    End Function

    ''' <summary>
    ''' Creates a GetMySQLCommand with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMySQLExecuteNonQuery(ByVal CommandText As String) As MySqlCommand
        Dim comm As New MySqlCommand
        comm.Connection = GetMySQLConnection()
        comm.CommandText = CommandText
        comm.Connection.Open()
        comm.ExecuteNonQuery()
        comm.Connection.Close()
        Return comm
    End Function

    ''' <summary>
    ''' Creates a GetMySQLCommand with the given CommandText and associates it with the site's Live or TestDB as appropriate.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetMySQLExecuteNonQuery(ByVal CommandText As String, ByVal ParamArray params As MySqlParameter()) As MySqlCommand
        Dim comm As New MySqlCommand
        comm.Connection = GetMySQLConnection()
        comm.CommandText = CommandText
        For Each param As MySqlParameter In params
            comm.Parameters.Add(param)
        Next
        comm.Connection.Open()
        comm.ExecuteNonQuery()
        comm.Connection.Close()
        Return comm
    End Function

    ''' <summary>
    ''' Tests the MySQLconnection with a select * FROM TABLE command
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsMySQLConnectionValid(ByVal CommandText As String) As Boolean
        Dim DA As MySqlDataAdapter = GetMySQLDataAdapter(CommandText)
        Dim dt As New Data.DataTable

        Try
            DA.Fill(dt)
        Catch ex As Exception
            Return False
        End Try

        Return True
    End Function
End Class

